# 数据库函数
import pymysql

host = "192.168.2.50"
user = "root"
pwd = "111111"
db = "ATM"

def connect_db():
    conn = pymysql.connect(host, user, pwd, db)
    # 将内容实例化成一个字典模式的游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return {"conn": conn, "cursor": cursor}

def select_sql(sql):
    connection = connect_db()
    conn, cursor = connection['conn'], connection['cursor']
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result
    except Exception as e:
        raise e
    finally:
        cursor.close()
        conn.close()

def insert_sql(sql):
    connection = connect_db()
    conn, cursor = connection['conn'], connection['cursor']
    try:
        cursor.execute(sql)
        conn.commit()
        msg = "数据插入成功"
        return msg
    except Exception as e:
        # 数据回滚，是数据库中事物的执行，有一条插入失败，所有的都不插入，数据库回滚到操作之前的样子
        conn.rollback()
        msg = "数据库出现问题，请稍后"
        return msg
    finally:
        cursor.close()
        conn.close()

def updata_sql(sql):
    connection = connect_db()
    conn, cursor = connection['conn'], connection['cursor']
    try:
        cursor.execute(sql)
        conn.commit()
        msg = "数据更新成功"
        return msg
    except Exception as e:
        conn.rollback()
        msg = "数据更新失败，请稍后"
        return msg
        # raise e
    finally:
        cursor.close()
        conn.close()

def select_sql_all(sql):
    connection = connect_db()
    conn, cursor = connection['conn'], connection['cursor']
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result
    except Exception as e:
        raise e
    finally:
        cursor.close()
        conn.close()